import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import '../models/contact_model.dart';
import '../models/repair_order_model.dart';

/// 数据库服务类
/// 负责本地SQLite数据库的创建、升级和CRUD操作
class DatabaseService {
  static Database? _database;
  static const String _dbName = 'autore.db';
  static const int _dbVersion = 10; // 匹配Android项目的版本

  /// 获取数据库实例
  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDB();
    return _database!;
  }

  /// 初始化数据库
  Future<Database> _initDB() async {
    String path = join(await getDatabasesPath(), _dbName);
    return await openDatabase(
      path,
      version: _dbVersion,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
    );
  }

  /// 创建数据库表
  Future<void> _onCreate(Database db, int version) async {
    // 创建客户表
    await db.execute('''
      CREATE TABLE IF NOT EXISTS contact (
        id TEXT PRIMARY KEY,
        carcode TEXT,
        name TEXT,
        tel TEXT,
        cartype TEXT,
        owner TEXT,
        inserttime TEXT,
        isbindweixin TEXT,
        weixinopenid TEXT,
        vin TEXT,
        carregistertime TEXT,
        headurl TEXT,
        safecompany TEXT,
        safenexttime TEXT,
        yearchecknexttime TEXT,
        tqTime1 TEXT,
        tqTime2 TEXT,
        key TEXT,
        isVip TEXT,
        carId TEXT,
        safecompany3 TEXT,
        safenexttime3 TEXT,
        tqTime3 TEXT,
        safetiptime3 TEXT
      )
    ''');

    // 创建工单表
    await db.execute('''
      CREATE TABLE IF NOT EXISTS repairHistory (
        id TEXT PRIMARY KEY,
        carcode TEXT,
        totalkm TEXT,
        repairtime TEXT,
        repairtype TEXT,
        addition TEXT,
        tipcircle TEXT,
        isclosetip TEXT,
        circle TEXT,
        isreaded TEXT,
        owner TEXT,
        inserttime TEXT
      )
    ''');
  }

  /// 数据库升级
  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    // 处理数据库升级逻辑
    if (oldVersion < newVersion) {
      // 删除旧表并重新创建（简化处理）
      await db.execute('DROP TABLE IF EXISTS contact');
      await db.execute('DROP TABLE IF EXISTS repairHistory');
      await _onCreate(db, newVersion);
    }
  }

  // ==================== 客户管理 ====================

  /// 添加客户
  Future<void> insertContact(Contact contact) async {
    final db = await database;
    await db.insert('contact', contact.toJson(), conflictAlgorithm: ConflictAlgorithm.replace);
  }

  /// 更新客户
  Future<void> updateContact(Contact contact) async {
    final db = await database;
    await db.update('contact', contact.toJson(), where: 'id = ?', whereArgs: [contact.id]);
  }

  /// 删除客户
  Future<void> deleteContact(String id) async {
    final db = await database;
    await db.delete('contact', where: 'id = ?', whereArgs: [id]);
  }

  /// 删除所有客户
  Future<void> deleteAllContacts() async {
    final db = await database;
    await db.delete('contact');
  }

  /// 获取所有客户
  Future<List<Contact>> getContacts() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query('contact');
    return List.generate(maps.length, (i) {
      return Contact.fromJson(maps[i]);
    });
  }

  /// 根据ID获取客户
  Future<Contact?> getContactById(String id) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query('contact', where: 'id = ?', whereArgs: [id]);
    if (maps.isNotEmpty) {
      return Contact.fromJson(maps.first);
    }
    return null;
  }

  /// 根据车主获取客户列表
  Future<List<Contact>> getContactsByOwner(String owner) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query('contact', where: 'owner = ?', whereArgs: [owner]);
    return List.generate(maps.length, (i) {
      return Contact.fromJson(maps[i]);
    });
  }

  // ==================== 工单管理 ====================

  /// 添加工单
  Future<void> insertRepairOrder(RepairOrder order) async {
    final db = await database;
    await db.insert('repairHistory', order.toJson(), conflictAlgorithm: ConflictAlgorithm.replace);
  }

  /// 更新工单
  Future<void> updateRepairOrder(RepairOrder order) async {
    final db = await database;
    await db.update('repairHistory', order.toJson(), where: 'id = ?', whereArgs: [order.id]);
  }

  /// 删除工单
  Future<void> deleteRepairOrder(String id) async {
    final db = await database;
    await db.delete('repairHistory', where: 'id = ?', whereArgs: [id]);
  }

  /// 删除所有工单
  Future<void> deleteAllRepairOrders() async {
    final db = await database;
    await db.delete('repairHistory');
  }

  /// 获取所有工单
  Future<List<RepairOrder>> getRepairOrders() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query('repairHistory');
    return List.generate(maps.length, (i) {
      return RepairOrder.fromJson(maps[i]);
    });
  }

  /// 根据ID获取工单
  Future<RepairOrder?> getRepairOrderById(String id) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query('repairHistory', where: 'id = ?', whereArgs: [id]);
    if (maps.isNotEmpty) {
      return RepairOrder.fromJson(maps.first);
    }
    return null;
  }

  /// 根据车主获取工单列表
  Future<List<RepairOrder>> getRepairOrdersByOwner(String owner) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query('repairHistory', where: 'owner = ?', whereArgs: [owner]);
    return List.generate(maps.length, (i) {
      return RepairOrder.fromJson(maps[i]);
    });
  }

  /// 根据车牌号获取工单列表
  Future<List<RepairOrder>> getRepairOrdersByCarCode(String carCode) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query('repairHistory', where: 'carcode = ?', whereArgs: [carCode]);
    return List.generate(maps.length, (i) {
      return RepairOrder.fromJson(maps[i]);
    });
  }

  // ==================== 数据统计 ====================

  /// 获取客户总数
  Future<int> getContactCount() async {
    final db = await database;
    final result = await db.rawQuery('SELECT COUNT(*) as count FROM contact');
    return Sqflite.firstIntValue(result) ?? 0;
  }

  /// 获取工单总数
  Future<int> getRepairOrderCount() async {
    final db = await database;
    final result = await db.rawQuery('SELECT COUNT(*) as count FROM repairHistory');
    return Sqflite.firstIntValue(result) ?? 0;
  }

  /// 获取今日工单数
  Future<int> getTodayRepairOrderCount() async {
    final db = await database;
    final today = DateTime.now().toIso8601String().split('T')[0];
    final result = await db.rawQuery('SELECT COUNT(*) as count FROM repairHistory WHERE DATE(repairtime) = ?', [today]);
    return Sqflite.firstIntValue(result) ?? 0;
  }

  // ==================== 数据库维护 ====================

  /// 关闭数据库
  Future<void> close() async {
    final db = _database;
    if (db != null) {
      await db.close();
      _database = null;
    }
  }

  /// 清空所有数据
  Future<void> clearAllData() async {
    final db = await database;
    await db.delete('contact');
    await db.delete('repairHistory');
  }

  /// 删除数据库
  Future<void> deleteDatabase() async {
    await close();
    String path = join(await getDatabasesPath(), _dbName);
    await databaseFactory.deleteDatabase(path);
  }
}